package csc.fresher.dao.impl;

/**
 * @author nam
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import csc.fresher.dao.DepositAccountTransactionDao;
import csc.fresher.entity.AccountTransactionState;
import csc.fresher.entity.CustomerAccount;
import csc.fresher.entity.DepositTransaction;
import csc.fresher.entity.TransactionState;
import csc.fresher.entity.TransactionType;

@Repository
public class DepositAccountTransactionDaoImpl implements
		DepositAccountTransactionDao {
	@Autowired
	DataSource dataSource;

	private static final Logger logger = Logger
			.getLogger(DepositAccountTransactionDaoImpl.class);

	@Override
	public long createTransaction(DepositTransaction tx) {
		String query = "INSERT INTO `DepositTransaction` (`Funds`,`BeginTime`,`EndTime`,`TransactionType`,"
				+ "`ReceiverID`,`SenderID`,`TransactionState`,`CurrentBalance`,`AfterBalance`,`DepositAccountNumber`,"
				+ "`CreatedBy`,`ApprovedBy`,`isInternetTransaction`,`Content`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		// String query2 =
		// "UPDATE DepositAccount SET `AccountTransactionState` = ? where `DepositAccountNumber` = ?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {

			connection = dataSource.getConnection();
			connection.setAutoCommit(false);

			// insert transaction to db
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setDouble(1, tx.getFunds());
			preparedStatement.setTimestamp(2, tx.getBeginTime());
			preparedStatement.setTimestamp(3, tx.getEndTime());
			preparedStatement.setString(4, tx.getTransactionType().getType());
			preparedStatement.setLong(5, tx.getReceiverID());
			preparedStatement.setLong(6, tx.getSenderID());
			preparedStatement.setString(7, tx.getTransactionState().getState());
			preparedStatement.setDouble(8, tx.getCurrentBalance());
			preparedStatement.setDouble(9, tx.getAfterBalance());
			preparedStatement.setLong(10, tx.getDepositAccountNumber());
			preparedStatement.setLong(11, tx.getCreatedBy());
			preparedStatement.setLong(11, tx.getApprovedBy());
			preparedStatement.setBoolean(13, tx.isInternetTransaction());
			preparedStatement.setString(14, tx.getContent());
			preparedStatement.execute();
			ResultSet generateKey = preparedStatement.getGeneratedKeys();

			// update account transaction state
			/*
			 * preparedStatement2 = connection.prepareStatement(query2);
			 * preparedStatement2.setString(1,
			 * AccountTransactionState.HOLD.getState());
			 * if(tx.getDepositAccountNumber() != 0){
			 * preparedStatement2.setLong(2, tx.getDepositAccountNumber()); }
			 * else{ preparedStatement2.setLong(2,
			 * tx.getDepositAccountNumber()); } preparedStatement2.execute();
			 */

			connection.commit();
			if (generateKey.next()) {
				return generateKey.getLong(1);
			} else
				return 0L;
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (preparedStatement2 != null) {
					preparedStatement2.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return 0L;
	}

	@Override
	public void removeTransaction(long txID, long adminID, long accountID) {
		// TODO Auto-generated method stub

	}

	@Override
	public void approveTransaction(long txID, long adminID, long accountID) {

		String query = "UPDATE DepositTransaction SET `TransactionState` = ?,`ApprovedBy` = ?, `EndTime` = ? where `TransactionID` = ?";

		String query2 = "UPDATE DepositAccount SET `AccountTransactionState` = ? where `DepositAccountNumber` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			connection.setAutoCommit(false);
			// update transaction info
			preparedStatement.setString(1, TransactionState.DONE.toString());
			preparedStatement.setLong(2, adminID);
			preparedStatement.setTimestamp(3,
					new Timestamp(System.currentTimeMillis()));
			preparedStatement.setLong(4, txID);

			preparedStatement.execute();

			// update account state
			preparedStatement2 = connection.prepareStatement(query2);
			preparedStatement2.setString(1,
					AccountTransactionState.ACTIVE.getState());
			preparedStatement2.setLong(2, accountID);

			preparedStatement2.execute();

			connection.commit();
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (preparedStatement2 != null) {
					preparedStatement2.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}

	}

	@Override
	public void denyTransaction(long txID, long adminID, long accountID) {
		String query = "UPDATE DepositTransaction SET `TransactionState` = ?,`ApprovedBy` = ?, `EndTime` = ? where `TransactionID` = ?";

		String query2 = "UPDATE DepositAccount SET `AccountTransactionState` = ? where `DepositAccountNumber` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			connection.setAutoCommit(false);

			// update transaction info
			preparedStatement.setString(1, TransactionState.DENY.toString());
			preparedStatement.setLong(2, adminID);
			preparedStatement.setTimestamp(3,
					new Timestamp(System.currentTimeMillis()));
			preparedStatement.setLong(4, txID);

			preparedStatement.execute();

			// update account state
			preparedStatement2 = connection.prepareStatement(query2);
			preparedStatement2.setString(1,
					AccountTransactionState.ACTIVE.getState());
			preparedStatement2.setLong(2, accountID);

			preparedStatement2.execute();

			connection.commit();
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (preparedStatement2 != null) {
					preparedStatement2.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}

	}

	@Override
	public List<DepositTransaction> searchTransactionByAccountID(long accountID) {
		String query = "Select * from DepositTransaction where `DepositAccountNumber`= ? ";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, accountID);

			ResultSet rs = preparedStatement.executeQuery();
			List<DepositTransaction> listTransaction = new ArrayList<DepositTransaction>();
			DepositTransaction tx = null;
			while (rs.next()) {
				tx = getTransactionFromResultSet(rs);
				if (tx != null) {
					listTransaction.add(tx);
				}
			}
			return listTransaction;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return new ArrayList<DepositTransaction>();
	}

	@Override
	public List<DepositTransaction> searchTransactionByCustomerCardID(
			long customerCardID) {
		String query = "Select DISTINCT * from "
				+ " DepositTransaction t LEFT JOIN  DepositAccount d on t.`DepositAccountNumber` = d.`DepositAccountNumber`"
				+ " LEFT JOIN Customer c on c.`CustomerID` = d.`Customer_CustomerID`"
				+ " where c.`IDCardNumber` = ? "
				+ " ORDER BY t.`BeginTime` DESC";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, customerCardID);
			ResultSet rs = preparedStatement.executeQuery();
			List<DepositTransaction> listTransaction = new ArrayList<DepositTransaction>();
			DepositTransaction tx = null;
			while (rs.next()) {
				tx = getTransactionFromResultSet(rs);
				if (tx != null) {
					listTransaction.add(tx);
				}
			}
			return listTransaction;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return new ArrayList<DepositTransaction>();
	}

	private DepositTransaction getTransactionFromResultSet(ResultSet rs) {
		DepositTransaction tx = new DepositTransaction();
		try {
			tx.setTransactionID(rs.getLong("TransactionID"));
			tx.setFunds(rs.getDouble("Funds"));
			tx.setBeginTime(rs.getTimestamp("BeginTime"));
			tx.setEndTime(rs.getTimestamp("EndTime"));
			tx.setTransactionType(TransactionType.valueOf(rs.getString(
					"TransactionType").toUpperCase()));
			tx.setReceiverID(rs.getLong("ReceiverID"));
			tx.setSenderID(rs.getLong("SenderID"));
			tx.setTransactionState(TransactionState.valueOf(rs.getString(
					"TransactionState").toUpperCase()));
			tx.setCurrentBalance(rs.getDouble("CurrentBalance"));
			tx.setAfterBalance(rs.getDouble("AfterBalance"));
			tx.setDepositAccountNumber(rs.getLong("DepositAccountNumber"));
			tx.setCreatedBy(rs.getInt("CreatedBy"));
			tx.setApprovedBy(rs.getInt("ApprovedBy"));
			tx.setInternetTransaction(rs.getBoolean("isInternetTransaction"));
			tx.setRelatedTransaction(rs.getLong("RelatedTransaction"));
			tx.setContent(rs.getString("Content"));

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return tx;
	}

	@Override
	public List<DepositTransaction> searchTransactionByCustomerCardIDInGivenPeriod(
			long customerCardID, Timestamp beginDate, Timestamp endDate) {
		String query = "Select DISTINCT * from "
				+ " DepositTransaction t LEFT JOIN  DepositAccount d on t.`DepositAccountNumber` = d.`DepositAccountNumber`"
				+ " LEFT JOIN Customer c on c.`CustomerID` = d.`Customer_CustomerID`"
				+ " WHERE c.`IDCardNumber` = ? and ((t.`BeginTime` >= ? and t.`BeginTime` <= ?) OR (t.`EndTime` >= ? and t.`EndTime` <= ?))"
				+ " ORDER BY t.`BeginTime` DESC";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, customerCardID);
			preparedStatement.setTimestamp(2, beginDate);
			preparedStatement.setTimestamp(3, endDate);
			preparedStatement.setTimestamp(4, beginDate);
			preparedStatement.setTimestamp(5, endDate);
			ResultSet rs = preparedStatement.executeQuery();
			List<DepositTransaction> listTransaction = new ArrayList<DepositTransaction>();
			DepositTransaction tx = null;
			while (rs.next()) {
				tx = getTransactionFromResultSet(rs);
				if (tx != null) {
					listTransaction.add(tx);
				}
			}
			return listTransaction;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return new ArrayList<DepositTransaction>();
	}

	public List<DepositTransaction> searchTransactionByDepositAccountIdInGivenPeriod(
			long depositAccountNumber, Timestamp beginDate, Timestamp endDate,
			boolean showInternet, int offset, int rowReturn) {
		String query = "SELECT * FROM DepositTransaction WHERE `DepositAccountNumber` =? AND `EndTime`>= ? AND `BeginTime` <= ? LIMIT ?,? ";
		if (showInternet) {
			query = "SELECT * FROM DepositTransaction WHERE `DepositAccountNumber` =? AND `EndTime`>= ? AND `BeginTime` <= ? AND `isInternetTransaction`=? LIMIT ?,? ";
		}
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, depositAccountNumber);
			preparedStatement.setTimestamp(2, beginDate);
			preparedStatement.setTimestamp(3, endDate);
			if (showInternet) {
				preparedStatement.setInt(4, 1);
				preparedStatement.setInt(5, offset);
				preparedStatement.setInt(6, rowReturn);
			} else {
				preparedStatement.setInt(4, offset);
				preparedStatement.setInt(5, rowReturn);
			}

			ResultSet rs = preparedStatement.executeQuery();
			List<DepositTransaction> listTransaction = new ArrayList<DepositTransaction>();
			DepositTransaction tx = null;
			while (rs.next()) {
				tx = getTransactionFromResultSet(rs);
				if (tx != null) {
					listTransaction.add(tx);
				}
			}
			return listTransaction;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return new ArrayList<DepositTransaction>();
	}

	public int getNumberOfTransactionByDepositAccountIdInGivenPeriod(
			long depositAccountNumber, Timestamp beginDate, Timestamp endDate,
			boolean showInternet) {
		int returnNum = 0;
		String query = "SELECT COUNT(*) FROM DepositTransaction WHERE `DepositAccountNumber` =? AND `EndTime`>= ? AND `BeginTime` <= ? ";
		if (showInternet) {
			query = "SELECT COUNT(*) FROM DepositTransaction WHERE `DepositAccountNumber` =? AND `EndTime`>= ? AND `BeginTime` <= ? AND `isInternetTransaction`=?";
		}

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, depositAccountNumber);
			preparedStatement.setTimestamp(2, beginDate);
			preparedStatement.setTimestamp(3, endDate);
			if (showInternet) {
				preparedStatement.setInt(4, 1);
			}
			ResultSet rs = preparedStatement.executeQuery();
			if (rs.next()) {
				returnNum = rs.getInt(1);
			}
			return returnNum;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return returnNum;
	}

	/**
	 * return transaction id of sender
	 */

	@Override
	public long createTransactionForTransferFunds(DepositTransaction transaction) {
		String query = "INSERT INTO `DepositTransaction` (`Funds`,`BeginTime`,`EndTime`,`TransactionType`,"
				+ "`ReceiverID`, `SenderID`, `TransactionState`,`CurrentBalance`,`AfterBalance`,`DepositAccountNumber`,"
				+ "`CreatedBy`,`ApprovedBy`,`isInternetTransaction`,`Content`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		String query2 = "UPDATE DepositAccount SET `DepositAccountBalance` = `DepositAccountBalance` - ? where  `DepositAccountNumber` = ?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {

			connection = dataSource.getConnection();
			connection.setAutoCommit(false);

			// insert transaction for sender
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setDouble(1, transaction.getFunds());
			preparedStatement.setTimestamp(2, transaction.getBeginTime());
			preparedStatement.setTimestamp(3, transaction.getEndTime());
			preparedStatement.setString(4, transaction.getTransactionType()
					.getType());
			preparedStatement.setLong(5, transaction.getReceiverID());
			preparedStatement.setLong(6, transaction.getSenderID());
			preparedStatement.setString(7, transaction.getTransactionState()
					.getState());
			preparedStatement.setDouble(8, transaction.getCurrentBalance());
			preparedStatement.setDouble(9, transaction.getAfterBalance());
			preparedStatement
					.setLong(10, transaction.getDepositAccountNumber());
			preparedStatement.setLong(11, transaction.getCreatedBy());
			preparedStatement.setLong(12, transaction.getApprovedBy());
			preparedStatement.setBoolean(13,
					transaction.isInternetTransaction());
			preparedStatement.setString(14, transaction.getContent());

			preparedStatement.execute();
			ResultSet generateKey = preparedStatement.getGeneratedKeys();

			// minus account balance of sender
			preparedStatement2 = connection.prepareStatement(query2);
			preparedStatement2.setDouble(1, transaction.getFunds());
			preparedStatement2
					.setLong(2, transaction.getDepositAccountNumber());

			preparedStatement2.execute();

			long txFromID = 0;
			if (generateKey.next()) {
				txFromID = generateKey.getLong(1);
			} else {
				connection.rollback();
			}

			connection.commit();
			return txFromID;
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
				return 0;
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (preparedStatement2 != null) {
					preparedStatement2.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return 0;

	}

	/**
	 * unsused
	 * 
	 * @param transactionFrom
	 * @param transactionTo
	 * @return
	 */

	/*
	 * public long createTransactionForTransferFundsOld( DepositTransaction
	 * transactionFrom, DepositTransaction transactionTo) { String query =
	 * "INSERT INTO `DepositTransaction` (`Funds`,`BeginTime`,`EndTime`,`TransactionType`,"
	 * +
	 * "`ReceiverID`, `SenderID`, `TransactionState`,`CurrentBalance`,`AfterBalance`,`DepositAccountNumber`,"
	 * +
	 * "`CreatedBy`,`ApprovedBy`,`isInternetTransaction`,`Content`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
	 * ;
	 * 
	 * String query2 =
	 * "INSERT INTO `DepositTransaction` (`Funds`,`BeginTime`,`EndTime`,`TransactionType`,"
	 * +
	 * " `ReceiverID`,`SenderID`, `TransactionState`,`CurrentBalance`,`AfterBalance`,`DepositAccountNumber`,"
	 * +
	 * "`CreatedBy`,`ApprovedBy`,`isInternetTransaction`,`Content`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
	 * ;
	 * 
	 * // String query3 = //
	 * "UPDATE DepositAccount SET `AccountTransactionState` = ? where `DepositAccountNumber` = ?"
	 * ; String query3 =
	 * "UPDATE DepositAccount SET `DepositAccountBalance` = `DepositAccountBalance` - ? where  `DepositAccountNumber` = ?"
	 * ; Connection connection = null; PreparedStatement preparedStatement =
	 * null; PreparedStatement preparedStatement2 = null; PreparedStatement
	 * preparedStatement3 = null; PreparedStatement preparedStatement4 = null;
	 * PreparedStatement preparedStatement5 = null; try {
	 * 
	 * connection = dataSource.getConnection(); connection.setAutoCommit(false);
	 * 
	 * // insert transaction for sender preparedStatement =
	 * connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
	 * preparedStatement.setDouble(1, transactionFrom.getFunds());
	 * preparedStatement.setTimestamp(2, transactionFrom.getBeginTime());
	 * preparedStatement.setTimestamp(3, transactionFrom.getEndTime());
	 * preparedStatement.setString(4, transactionFrom.getTransactionType()
	 * .getType()); preparedStatement.setLong(5,
	 * transactionFrom.getReceiverID()); preparedStatement.setLong(6,
	 * transactionFrom.getSenderID()); preparedStatement.setString(7,
	 * transactionFrom .getTransactionState().getState());
	 * preparedStatement.setDouble(8, transactionFrom.getCurrentBalance());
	 * preparedStatement.setDouble(9, transactionFrom.getAfterBalance());
	 * preparedStatement.setLong(10, transactionFrom.getDepositAccountNumber());
	 * preparedStatement.setLong(11, transactionFrom.getCreatedBy());
	 * preparedStatement.setLong(12, transactionFrom.getApprovedBy());
	 * preparedStatement.setBoolean(13,
	 * transactionFrom.isInternetTransaction()); preparedStatement.setString(14,
	 * transactionFrom.getContent());
	 * 
	 * preparedStatement.execute(); ResultSet generateKey =
	 * preparedStatement.getGeneratedKeys();
	 * 
	 * // insert transaction for receiver preparedStatement2 =
	 * connection.prepareStatement(query2,Statement.RETURN_GENERATED_KEYS);
	 * preparedStatement2.setDouble(1, transactionTo.getFunds());
	 * preparedStatement2.setTimestamp(2, transactionTo.getBeginTime());
	 * preparedStatement2.setTimestamp(3, transactionTo.getEndTime());
	 * preparedStatement2.setString(4, transactionTo.getTransactionType()
	 * .getType()); preparedStatement2.setLong(5,
	 * transactionTo.getReceiverID()); preparedStatement2.setLong(6,
	 * transactionTo.getSenderID()); preparedStatement2.setString(7,
	 * transactionTo.getTransactionState() .getState());
	 * preparedStatement2.setDouble(8, transactionTo.getCurrentBalance());
	 * preparedStatement2.setDouble(9, transactionTo.getAfterBalance());
	 * preparedStatement2.setLong(10, transactionTo.getDepositAccountNumber());
	 * preparedStatement2.setLong(11, transactionTo.getCreatedBy());
	 * preparedStatement2.setLong(12, transactionTo.getApprovedBy());
	 * preparedStatement2.setBoolean(13, transactionTo.isInternetTransaction());
	 * preparedStatement2.setString(14, transactionTo.getContent());
	 * 
	 * preparedStatement2.execute(); ResultSet generateKey2 =
	 * preparedStatement2.getGeneratedKeys();
	 * 
	 * // minus account balance of sender preparedStatement3 =
	 * connection.prepareStatement(query3); preparedStatement3.setDouble(1,
	 * transactionFrom.getFunds()); preparedStatement3.setLong(2,
	 * transactionFrom.getDepositAccountNumber());
	 * 
	 * preparedStatement3.execute();
	 * 
	 * 
	 * long txFromID = 0; long txToID = 0; if(generateKey.next() &&
	 * generateKey2.next()){ txFromID = generateKey.getLong(1); txToID =
	 * generateKey2.getLong(1); } else{ connection.rollback(); }
	 * 
	 * //update related transaction for both String query4 =
	 * "UPDATE DepositTransaction SET `RelatedTransaction` = ? WHERE `TransactionID` = ?"
	 * ;
	 * 
	 * 
	 * preparedStatement4 = connection.prepareStatement(query4);
	 * preparedStatement4.setLong(1, txToID); preparedStatement4.setLong(2,
	 * txFromID); preparedStatement4.execute();
	 * 
	 * preparedStatement5 = connection.prepareStatement(query4);
	 * preparedStatement5.setLong(1, txFromID); preparedStatement5.setLong(2,
	 * txToID); preparedStatement5.execute();
	 * 
	 * // update account transaction state
	 * 
	 * preparedStatement3 = connection.prepareStatement(query3);
	 * preparedStatement3.setString(1, AccountTransactionState.HOLD.getState());
	 * if(transactionFrom.getDepositAccountNumber() != 0){
	 * preparedStatement3.setLong(2, transactionFrom.getDepositAccountNumber());
	 * } else{ preparedStatement3.setLong(2,
	 * transactionFrom.getDepositAccountNumber()); }
	 * preparedStatement3.execute();
	 * 
	 * 
	 * connection.commit(); return txFromID; } catch (SQLException e) {
	 * logger.error(e); try { connection.rollback(); } catch (SQLException e1) {
	 * // TODO Auto-generated catch block e1.printStackTrace(); }
	 * e.printStackTrace(); } finally { try { if (preparedStatement != null) {
	 * preparedStatement.close(); } if (preparedStatement2 != null) {
	 * preparedStatement2.close(); } if (preparedStatement3 != null) {
	 * preparedStatement3.close(); } if (preparedStatement4 != null) {
	 * preparedStatement4.close(); } if (preparedStatement5 != null) {
	 * preparedStatement5.close(); } if (connection != null) {
	 * connection.close(); } } catch (SQLException e) { logger.error(e);
	 * e.printStackTrace(); } } return 0;
	 * 
	 * }
	 */

	@Override
	public long createTransactionForWithdrawFunds(DepositTransaction transaction) {
		String query = "INSERT INTO `DepositTransaction` (`Funds`,`BeginTime`,`EndTime`,`TransactionType`,"
				+ "`ReceiverID`,`SenderID`, `TransactionState`,`CurrentBalance`,`AfterBalance`,`DepositAccountNumber`,"
				+ "`CreatedBy`,`ApprovedBy`,`isInternetTransaction`,`Content`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		// String query2 =
		// "UPDATE DepositAccount SET `AccountTransactionState` = ? where `DepositAccountNumber` = ?";
		String query2 = "UPDATE DepositAccount SET `DepositAccountBalance` = `DepositAccountBalance` - ? where  `DepositAccountNumber` = ?";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {

			connection = dataSource.getConnection();
			connection.setAutoCommit(false);

			// insert transaction to db
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setDouble(1, transaction.getFunds());
			preparedStatement.setTimestamp(2, transaction.getBeginTime());
			preparedStatement.setTimestamp(3, transaction.getEndTime());
			preparedStatement.setString(4, transaction.getTransactionType()
					.getType());
			preparedStatement.setLong(5, transaction.getReceiverID());
			preparedStatement.setLong(6, transaction.getReceiverID());
			preparedStatement.setString(7, transaction.getTransactionState()
					.getState());
			preparedStatement.setDouble(8, transaction.getCurrentBalance());
			preparedStatement.setDouble(9, transaction.getAfterBalance());
			preparedStatement
					.setLong(10, transaction.getDepositAccountNumber());
			preparedStatement.setLong(11, transaction.getCreatedBy());
			preparedStatement.setLong(12, transaction.getApprovedBy());
			preparedStatement.setBoolean(13,
					transaction.isInternetTransaction());
			preparedStatement.setString(14, transaction.getContent());

			preparedStatement.execute();
			ResultSet generateKey = preparedStatement.getGeneratedKeys();

			// update account transaction state
			/*
			 * preparedStatement2 = connection.prepareStatement(query2);
			 * preparedStatement2.setString(1,
			 * AccountTransactionState.HOLD.getState());
			 * if(tx.getDepositAccountNumber() != 0){
			 * preparedStatement2.setLong(2, tx.getDepositAccountNumber()); }
			 * else{ preparedStatement2.setLong(2,
			 * tx.getDepositAccountNumber()); } preparedStatement2.execute();
			 */

			preparedStatement2 = connection.prepareStatement(query2);
			preparedStatement2.setDouble(1, transaction.getFunds());
			preparedStatement2
					.setLong(2, transaction.getDepositAccountNumber());
			System.out.println(preparedStatement2);
			preparedStatement2.execute();

			connection.commit();
			if (generateKey.next()) {
				return generateKey.getLong(1);
			} else
				return 0L;
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (preparedStatement2 != null) {
					preparedStatement2.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return 0L;
	}

	@Override
	public long createTransactionForAddFunds(DepositTransaction transaction) {
		String query = "INSERT INTO `DepositTransaction` (`Funds`,`BeginTime`,`EndTime`,`TransactionType`,"
				+ "`ReceiverID`,`SenderID`,`TransactionState`,`CurrentBalance`,`AfterBalance`,`DepositAccountNumber`,"
				+ "`CreatedBy`,`ApprovedBy`,`isInternetTransaction`,`Content`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {

			connection = dataSource.getConnection();
			connection.setAutoCommit(false);

			// insert transaction to db
			preparedStatement = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement.setDouble(1, transaction.getFunds());
			preparedStatement.setTimestamp(2, transaction.getBeginTime());
			preparedStatement.setTimestamp(3, transaction.getEndTime());
			preparedStatement.setString(4, transaction.getTransactionType()
					.getType());
			preparedStatement.setLong(5, transaction.getReceiverID());
			preparedStatement.setLong(6, transaction.getSenderID());
			preparedStatement.setString(7, transaction.getTransactionState()
					.getState());
			preparedStatement.setDouble(8, transaction.getCurrentBalance());
			preparedStatement.setDouble(9, transaction.getAfterBalance());
			preparedStatement
					.setLong(10, transaction.getDepositAccountNumber());
			preparedStatement.setLong(11, transaction.getCreatedBy());
			preparedStatement.setLong(12, transaction.getApprovedBy());
			preparedStatement.setBoolean(13,
					transaction.isInternetTransaction());
			preparedStatement.setString(14, transaction.getContent());

			preparedStatement.execute();
			ResultSet generateKey = preparedStatement.getGeneratedKeys();

			connection.commit();
			if (generateKey.next()) {
				return generateKey.getLong(1);
			} else
				return 0L;
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return 0L;
	}

	@Override
	public boolean approveTransctionForWithdrawFunds(
			DepositTransaction transaction, long adminID) {
		String query = "UPDATE DepositTransaction SET `TransactionState` = ?,`ApprovedBy` = ?, `EndTime` = ? where `TransactionID` = ?";

		// String query2 =
		// "UPDATE DepositAccount SET `AccountTransactionState` = ? where `DepositAccountNumber` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);

			// update transaction info
			preparedStatement.setString(1, TransactionState.DONE.toString());
			preparedStatement.setLong(2, adminID);
			preparedStatement.setTimestamp(3,
					new Timestamp(System.currentTimeMillis()));
			preparedStatement.setLong(4, transaction.getTransactionID());

			preparedStatement.execute();

			// update account state
			/*
			 * preparedStatement2 = connection.prepareStatement(query2);
			 * preparedStatement2.setString(1,
			 * AccountTransactionState.ACTIVE.getState());
			 * preparedStatement2.setLong(2, accountID);
			 * 
			 * preparedStatement2.execute();
			 */

			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return false;
	}

	@Override
	public boolean denyTransactionForWithdrawFunds(
			DepositTransaction transaction, CustomerAccount account,
			long adminID) {
		String query = "UPDATE DepositTransaction SET `TransactionState` = ?,`ApprovedBy` = ?, `EndTime` = ?,`CurrentBalance` = ?, `AfterBalance` = ? where `TransactionID` = ?";

		String query2 = "UPDATE DepositAccount SET `DepositAccountBalance` = `DepositAccountBalance` + ? where  `DepositAccountNumber` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			connection.setAutoCommit(false);
			// update transaction info
			preparedStatement.setString(1, TransactionState.DENY.toString());
			preparedStatement.setLong(2, adminID);
			preparedStatement.setTimestamp(3,
					new Timestamp(System.currentTimeMillis()));
			preparedStatement.setDouble(4, account.getAccountBalance());
			preparedStatement.setDouble(5, account.getAccountBalance()
					+ transaction.getFunds());
			preparedStatement.setLong(6, transaction.getTransactionID());

			preparedStatement.execute();

			// update account state
			preparedStatement2 = connection.prepareStatement(query2);
			preparedStatement2.setDouble(1, transaction.getFunds());
			preparedStatement2
					.setLong(2, transaction.getDepositAccountNumber());

			preparedStatement2.execute();

			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return false;
	}

	/**
	 * need customer account to get current balance
	 */
	@Override
	public boolean approveTransctionForAddFunds(DepositTransaction transaction,
			CustomerAccount account, long adminID) {
		String query = "UPDATE DepositTransaction SET `TransactionState` = ?,`ApprovedBy` = ?, `EndTime` = ?,`CurrentBalance` = ?, `AfterBalance` = ? where `TransactionID` = ?";

		String query2 = "UPDATE DepositAccount SET `DepositAccountBalance` = `DepositAccountBalance` + ? where  `DepositAccountNumber` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		PreparedStatement preparedStatement2 = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(query);

			// update transaction info
			preparedStatement.setString(1, TransactionState.DONE.toString());
			preparedStatement.setLong(2, adminID);
			preparedStatement.setTimestamp(3,
					new Timestamp(System.currentTimeMillis()));
			preparedStatement.setDouble(4, account.getAccountBalance());
			preparedStatement.setDouble(5, account.getAccountBalance()
					+ transaction.getFunds());
			preparedStatement.setLong(6, transaction.getTransactionID());

			preparedStatement.execute();

			// update account state
			preparedStatement2 = connection.prepareStatement(query2);
			preparedStatement2.setDouble(1, transaction.getFunds());
			preparedStatement2
					.setLong(2, transaction.getDepositAccountNumber());

			preparedStatement2.execute();

			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
				return false;
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (preparedStatement2 != null) {
					preparedStatement2.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return false;
	}

	@Override
	public boolean denyTransactionForAddFunds(DepositTransaction transaction,
			long adminID) {

		String query = "UPDATE DepositTransaction SET `TransactionState` = ?,`ApprovedBy` = ?, `EndTime` = ? where `TransactionID` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			connection.setAutoCommit(false);
			// update transaction info
			preparedStatement.setString(1, TransactionState.DENY.toString());
			preparedStatement.setLong(2, adminID);
			preparedStatement.setTimestamp(3,
					new Timestamp(System.currentTimeMillis()));
			preparedStatement.setLong(4, transaction.getTransactionID());

			preparedStatement.execute();

			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return false;
	}

	@Override
	public DepositTransaction getTransactionByID(long transactionID) {
		String query = "Select  * from DepositTransaction WHERE `TransactionID` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setLong(1, transactionID);
			ResultSet rs = preparedStatement.executeQuery();
			DepositTransaction tx = null;
			if (rs.next()) {
				tx = getTransactionFromResultSet(rs);
			}
			return tx;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public boolean approveTransactionForTransferFunds(
			DepositTransaction transaction, CustomerAccount receiver,
			long adminID) {

		// String query2 =
		// "UPDATE DepositTransaction SET `TransactionState` = ?,`ApprovedBy` = ?, `EndTime` = ?, `CurrentBalance` = ?, `AfterBalance` = ? WHERE `TransactionID` = ?";

		String query = "INSERT INTO `DepositTransaction` (`Funds`,`BeginTime`,`EndTime`,`TransactionType`,"
				+ " `ReceiverID`,`SenderID`, `TransactionState`,`CurrentBalance`,`AfterBalance`,`DepositAccountNumber`,"
				+ "`CreatedBy`,`ApprovedBy`,`isInternetTransaction`,`Content`,`RelatedTransaction`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		String query2 = "UPDATE DepositTransaction SET `TransactionState` = ?,`ApprovedBy` = ?, `EndTime` = ?, `RelatedTransaction` = ? where `TransactionID` = ?";

		String query3 = "UPDATE DepositAccount SET `DepositAccountBalance` = `DepositAccountBalance` + ? where  `DepositAccountNumber` = ?";

		Connection connection = null;

		PreparedStatement preparedStatement1 = null;
		PreparedStatement preparedStatement2 = null;
		PreparedStatement preparedStatement3 = null;
		try {
			connection = dataSource.getConnection();
			connection.setAutoCommit(false);

			Timestamp currentTimestamp = new Timestamp(
					System.currentTimeMillis());

			// create receiver's transaction
			preparedStatement1 = connection.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			preparedStatement1.setDouble(1, transaction.getFunds());
			preparedStatement1.setTimestamp(2, transaction.getBeginTime());
			preparedStatement1.setTimestamp(3, currentTimestamp);
			preparedStatement1.setString(4, TransactionType.RECEIVE.getType());
			preparedStatement1.setLong(5, transaction.getReceiverID());
			preparedStatement1.setLong(6, transaction.getSenderID());
			preparedStatement1.setString(7, TransactionState.DONE.getState());
			preparedStatement1.setDouble(8, receiver.getAccountBalance());
			preparedStatement1.setDouble(9, receiver.getAccountBalance()
					- transaction.getFunds());
			preparedStatement1.setLong(10, transaction.getReceiverID());
			preparedStatement1.setLong(11, transaction.getCreatedBy());
			preparedStatement1.setLong(12, transaction.getApprovedBy());
			preparedStatement1.setBoolean(13,
					transaction.isInternetTransaction());
			preparedStatement1.setString(14, transaction.getContent());
			preparedStatement1.setLong(15, transaction.getTransactionID());
			preparedStatement1.execute();
			ResultSet generateKey = preparedStatement1.getGeneratedKeys();
			long txReceiveID = 0;
			if (generateKey.next()) {
				txReceiveID = generateKey.getLong(1);
			}

			if (txReceiveID == 0) {
				connection.rollback();
				return false;
			}

			// update sender transaction info
			preparedStatement2 = connection.prepareStatement(query2);
			preparedStatement2.setString(1, TransactionState.DONE.toString());
			preparedStatement2.setLong(2, adminID);
			preparedStatement2.setTimestamp(3, currentTimestamp);
			preparedStatement2.setLong(4, txReceiveID);
			preparedStatement2.setLong(5, transaction.getTransactionID());
			preparedStatement2.execute();

			// update receiver account balance
			preparedStatement3 = connection.prepareStatement(query3);
			preparedStatement3.setDouble(1, transaction.getFunds());
			preparedStatement3.setLong(2, transaction.getReceiverID());
			preparedStatement3.execute();

			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
			try {
				connection.rollback();
				return false;
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}

		} finally {
			try {
				if (preparedStatement1 != null) {
					preparedStatement1.close();
				}
				if (preparedStatement2 != null) {
					preparedStatement2.close();
				}
				if (preparedStatement3 != null) {
					preparedStatement3.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return false;
	}

	@Override
	public boolean denyTransctionForTransferFunds(
			DepositTransaction transaction, CustomerAccount sender, long adminID) {

		String query = "UPDATE DepositTransaction SET `TransactionState` = ?,`ApprovedBy` = ?, `EndTime` = ?, `CurrentBalance` = ?, `AfterBalance` = ? WHERE `TransactionID` = ?";

		String query2 = "UPDATE DepositAccount SET `DepositAccountBalance` = `DepositAccountBalance` + ? where  `DepositAccountNumber` = ?";

		Connection connection = null;
		PreparedStatement preparedStatement1 = null;
		PreparedStatement preparedStatement2 = null;
		try {
			connection = dataSource.getConnection();

			connection.setAutoCommit(false);

			// update sender transaction info
			preparedStatement1 = connection.prepareStatement(query);
			preparedStatement1.setString(1, TransactionState.DENY.toString());
			preparedStatement1.setLong(2, adminID);
			preparedStatement1.setTimestamp(3,
					new Timestamp(System.currentTimeMillis()));
			preparedStatement1.setDouble(4, 0);
			preparedStatement1.setDouble(5, 0);
			preparedStatement1.setLong(6, transaction.getTransactionID());
			preparedStatement1.execute();

			// update receiver account balance
			preparedStatement2 = connection.prepareStatement(query2);
			preparedStatement2.setDouble(1, transaction.getFunds());
			preparedStatement2
					.setLong(2, transaction.getDepositAccountNumber());
			preparedStatement2.execute();

			connection.commit();
			return true;
		} catch (SQLException e) {
			logger.error(e);
			try {
				connection.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement1 != null) {
					preparedStatement1.close();
				}
				if (preparedStatement2 != null) {
					preparedStatement2.close();
				}
				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return false;
	}

	@Override
	public List<DepositTransaction> getDepositTransactionByState(
			TransactionState transactionState) {
		String query = "Select * from DepositTransaction where `TransactionState`= ? ";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = dataSource.getConnection();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, transactionState.getState());

			ResultSet rs = preparedStatement.executeQuery();
			List<DepositTransaction> listTransaction = new ArrayList<DepositTransaction>();
			DepositTransaction tx = null;
			while (rs.next()) {
				tx = getTransactionFromResultSet(rs);
				if (tx != null) {
					listTransaction.add(tx);
				}
			}
			return listTransaction;
		} catch (SQLException e) {
			logger.error(e);
			e.printStackTrace();
		} finally {
			try {
				if (preparedStatement != null) {
					preparedStatement.close();
				}

				if (connection != null) {
					connection.close();
				}
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}
		return new ArrayList<DepositTransaction>();
	}

}
